/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package admintool.imp03_data;

import admintool.AdminToolView;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Administrator
 */
public class IMP03_Skill {

    private class Skill {

        private int skillID = 0;
        private String skillName = "";
        private String skillDesc = "";
        
        public Skill(int skillID, String skillName, String skillDesc) {
            this.skillID = skillID;
            this.skillName = skillName;
            this.skillDesc = skillDesc;
        }

        public int getSkillID() {
            return skillID;
        }

        public String getSkillDesc() {
            return skillDesc;
        }

        public String getSkillName() {
            return skillName;
        }
    }
    
    private static ArrayList<IMP03_Skill> skills = new ArrayList<IMP03_Skill>();
    private ArrayList<IMP03_Vacancy> vacancySkills = new ArrayList<IMP03_Vacancy>();
    private ArrayList<IMP03_Candidate> candidateSkills = new ArrayList<IMP03_Candidate>();

    /**
     * 
     * @return
     */
    public static IMP03_Skill getEmptySkillObject(){
        return new IMP03_Skill(-1, AdminToolView.EMPTY, AdminToolView.EMPTY);
    }
    
    /**
     * 
     * @param reload
     * @return
     */
    public static ArrayList<IMP03_Skill> getAllAvailableSkillsFromDB(boolean reload){
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            if(reload == true || skills.isEmpty()){
                skills.clear();
                ps = AdminToolView.getDBConnection().prepareStatement("SELECT * FROM skill");
                rs = ps.executeQuery();
                while(rs.next()){
                    skills.add(new IMP03_Skill(rs.getInt(1), rs.getString(2), rs.getString(3)));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
        return skills;
    }
    
    private Skill skill = null;
    
    /**
     * 
     * @param skillId
     * @param skillName
     * @param skillDesc
     */
    public IMP03_Skill(int skillId, String skillName, String skillDesc) {
        this.skill = new Skill(skillId, skillName, skillDesc);
    }
    
    /**
     * 
     * @return
     */
    public int getSkillID(){
        return skill.getSkillID();
    }
    
    /**
     * 
     * @return
     */
    public String getSkillName(){
        return skill.getSkillName();
    }

    /**
     * 
     * @return
     */
    public String getSkillDesc(){
        return skill.getSkillDesc();
    }
    
    @Override
    public String toString() {
        return skill.getSkillName();
    }
  
    /**
     * 
     * @return
     */
    public ArrayList<IMP03_Vacancy> getVacancySkills(){
        initSkillVacancies();
        return vacancySkills;    
    }  
    
    /**
     * 
     * @return
     */
    public ArrayList<IMP03_Candidate> getCandidateSkills(){
        initSkillCandidates();
        return candidateSkills;    
    }  
    
    /**
     * 
     * @param candidateID
     * @return
     */
    public int getSkillLevelForCandidate(int candidateID){
        PreparedStatement ps = null;
        ResultSet rs = null;
        int skillLevel = 0;
        
        try {
            ps = AdminToolView.getDBConnection().prepareStatement("SELECT SkillLevel FROM candidate_skills WHERE SkillID = ? AND CandidateID = ?");
            ps.setInt(1, getSkillID());
            ps.setInt(2, candidateID);
            rs = ps.executeQuery();
            while(rs.next()){
                skillLevel = rs.getInt(1);
                break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
        return skillLevel;
    }
    
    /**
     * 
     * @param vacancyID
     * @return
     */
    public int getSkillLevelForVacancy(int vacancyID){
        PreparedStatement ps = null;
        ResultSet rs = null;
        int skillLevel = 0;
        
        try {
            ps = AdminToolView.getDBConnection().prepareStatement("SELECT SkillLevel FROM vacancy_skills WHERE SkillID = ? AND VacancyID = ?");
            ps.setInt(1, getSkillID());
            ps.setInt(2, vacancyID);
            rs = ps.executeQuery();
            while(rs.next()){
                skillLevel = rs.getInt(1);
                break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
        return skillLevel;
    }
    
    private void initSkillVacancies() {
        PreparedStatement ps = null;
        ResultSet rs = null;
        vacancySkills.clear();
        
        try {
            ps = AdminToolView.getDBConnection().prepareStatement("SELECT VacancyID FROM vacancy_skills WHERE SkillID = ?");
            ps.setInt(1, getSkillID());
            rs = ps.executeQuery();
            ArrayList<Integer> vacancyIDs = new ArrayList<Integer>();
            while(rs.next()){
                vacancyIDs.add(rs.getInt(1));
            }
            
            for(IMP03_Vacancy v : IMP03_Vacancy.getAllAvailableVacanciesFromDB(false)){
                if(vacancyIDs.contains(v.getVacancyID())){
                    vacancySkills.add(v);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
    }    
    
    private void initSkillCandidates() {
        PreparedStatement ps = null;
        ResultSet rs = null;
        candidateSkills.clear();
        
        try {
            ps = AdminToolView.getDBConnection().prepareStatement("SELECT CandidateID FROM candidate_skills WHERE SkillID = ?");
            ps.setInt(1, getSkillID());
            rs = ps.executeQuery();
            ArrayList<Integer> candidateIDs = new ArrayList<Integer>();
            while(rs.next()){
                candidateIDs.add(rs.getInt(1));
            }
            
            for(IMP03_Candidate c : IMP03_Candidate.getAllAvailableCandidatesFromDB(false)){
                if(candidateIDs.contains(c.getCandidateID())){
                    candidateSkills.add(c);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
    }       
}
